﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Data.Common;
using System.Data.OleDb;

namespace PDFGenerator
{
    class GeneratorMain
    {
        public static List<Product> products = new List<Product>();
        public static Dictionary<int, NamePair> dic = new Dictionary<int, NamePair>();

        public static void FindExcels(string dirPath)
        {
            DirectoryInfo dInfo = new DirectoryInfo(dirPath);

            FileInfo[] files = dInfo.GetFiles(@"*.xlsx");
            foreach (FileInfo file in files)
            {
                ReadExcel(file.FullName);
            }
        }

        public static void ReadExcel(string path)
        {
            string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=No;IMEX=1\";";

            OleDbConnection connection = new OleDbConnection(connectionString);
            using (connection)
            {
                OleDbCommand command = new OleDbCommand("SELECT * FROM [Sheet1$]", connection);
                using (command)
                {
                    connection.Open();
                    OleDbDataReader reader = command.ExecuteReader();
                    int rowsCount = 0;
                    string spName = "";
                    using (reader)
                    {
                        while (reader.Read())
                        {
                            if (rowsCount == 0)
                            {
                                spName = reader.GetValue(0).ToString();
                            }
                            else if (rowsCount != 1 && !reader.GetValue(0).ToString().StartsWith("Total"))
                            {
                                NamePair np = dic[Convert.ToInt32(reader.GetValue(0))];
                                products.Add(new Product(np.ProductName, reader.GetValue(1).ToString(), np.MeasureName,
                                                         reader.GetValue(2).ToString(), spName));
                            }
                            ++rowsCount;
                        }
                    }
                }
            }
        }

        static void Main()
        {
            
            SQLReader s = new SQLReader("localhost", "supermarket", "root", "rootroot");
            dic = s.GetData();
            FindExcels(@"C:\xp");

            PDFWriter.PrintSalesReport(PDFWriter.ConvertToString(products) , @"C:\xp\report.pdf");

        }
    }
}
